﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using NPOI.SS.UserModel;

namespace ExcelMergeTool
{
    public class MergerService
    {
        #region 获取保存文件路径
        public static string GetSavePath(string file)
        {
            int num = file.LastIndexOf("\\");
            string text = file.Substring(0, num);
            num = text.LastIndexOf("\\");
            text = text.Substring(0, num + 1) + "汇总表\\";
            if (!Directory.Exists(text))
            {
                Directory.CreateDirectory(text);
            }
            return text + "sum.xls";
        }
        #endregion

        #region 读取文件数据
        public static List<ExcelData> GetFileData(string filePath, int rowHeaderIndex, int columnHeaderIndex, 
            int dataStartRowIndex, int dataStartColumnIndex, int dataEndRowIndex, int dataEndColumnIndex)
        {
            var list = new List<ExcelData>();
            IWorkbook workbook = NPOIUtil.GetWorkbook(filePath);
            ISheet sheet = NPOIUtil.GetSheet(workbook);
            //获取头信息
            var rowHeaderDic = GetRowHeaderNameIndex(sheet, rowHeaderIndex, dataStartRowIndex, dataEndRowIndex);
            //获取列信息
            var columnHeaderDic = GetColumnHeaderNameIndex(sheet, columnHeaderIndex, dataStartColumnIndex, dataEndColumnIndex);
            //遍历行数据
            for (int rowIndex = dataStartRowIndex; rowIndex <= dataEndRowIndex; rowIndex++)
            {
                var row = NPOIUtil.GetRow(sheet, rowIndex);
                //遍历列数据
                for (int columnIndex = dataStartColumnIndex; columnIndex <= dataEndColumnIndex; columnIndex++ )
                {
                    var data = GetData(row, columnIndex, rowHeaderDic, columnHeaderDic, filePath);

                    list.Add(data);
                }
            }
            return list;
        }

        private static Dictionary<int, string> GetColumnHeaderNameIndex(ISheet sheet, int columnHeaderIndex, int dataStartColumnIndex, int dataEndColumnIndex)
        {
            var dic = new Dictionary<int, string>();
            IRow row = NPOIUtil.GetRow(sheet, columnHeaderIndex);
            for (int columnIndex = dataStartColumnIndex; columnIndex <= dataEndColumnIndex; columnIndex++)
            {
                object cellVaule = NPOIUtil.ReadCell(row, columnIndex);
                if (cellVaule != null && cellVaule.ToString() != string.Empty)
                {
                    if(!dic.ContainsValue(cellVaule.ToString()))
                    {
                        dic.Add(columnIndex, cellVaule.ToString());
                    }else
                    {
                        dic.Add(columnIndex, string.Format("{0}_{1}", cellVaule.ToString(), columnIndex));
                    }
                   
                }
                else
                {
                    dic.Add(columnIndex, columnIndex.ToString());
                }
            }

            return dic;
        }

        private static Dictionary<int, string> GetRowHeaderNameIndex(ISheet sheet, int rowHeaderIndex, int dataStartRowIndex, int dataEndRowIndex)
        {
            int endRowIndex = MergerService.GetEndRow(sheet);

            var dic = new Dictionary<int, string>();
          
            for (int rowIndex = dataStartRowIndex; rowIndex <= dataEndRowIndex; rowIndex++)
            {
                IRow row = NPOIUtil.GetRow(sheet, rowIndex);
                object cellVaule = NPOIUtil.ReadCell(row, rowHeaderIndex);
                if (cellVaule != null && cellVaule.ToString() != string.Empty)
                {
                    if (!dic.ContainsValue(cellVaule.ToString()))
                    {
                        dic.Add(rowIndex, cellVaule.ToString());
                    }
                    else
                    {
                        dic.Add(rowIndex, string.Format("{0}_{1}", cellVaule.ToString(), rowIndex));
                    }

                }
                else
                {
                    dic.Add(rowIndex, rowIndex.ToString());
                }
            }

            return dic;
        }

        private static ExcelData GetData(IRow row, int columnIndex, Dictionary<int, string> rowHeaderDic, Dictionary<int, string> columnHeaderDic, string fileName)
        {
            object cellVaule = NPOIUtil.ReadCell(row, columnIndex);
            string value = string.Empty;
            if (cellVaule != null && cellVaule.ToString() != string.Empty)
            {
                value = cellVaule.ToString();
            }
            var rowIndex = row.RowNum;
            return new ExcelData()
            {
                Value = value,
                ColumnIndex = columnIndex,
                RowIndex = rowIndex,
                RowHeaderName = rowHeaderDic[rowIndex],
                ColumnHeaderName = columnHeaderDic[columnIndex],
                SheetName = row.Sheet.SheetName,
                WorkbookName = fileName,
            };
        }

        private static int GetEndCol(ISheet sheet, int rowHeaderIndex)
        {
            IRow row = NPOIUtil.GetRow(sheet, rowHeaderIndex);
            return (int)row.LastCellNum;
        }

        private static int GetEndRow(ISheet sheet)
        {
            return sheet.LastRowNum;
        }
        
        #endregion

        #region 汇总数据

        public static void SaveSumData(string tempPath, List<ExcelData> list, int rowHeaderIndex, int columnHeaderIndex,
            int dataStartRowIndex, int dataStartColumnIndex, int dataEndRowIndex, int dataEndColumnIndex, out string savePath)
        {
            //获取保存路径
            savePath = GetSavePath(tempPath);

            string text = string.Format("{0}temp.xls", savePath.Substring(0, savePath.LastIndexOf("\\") + 1));
            if (File.Exists(text))
            {
                File.Delete(text);
            }
            if (File.Exists(savePath))
            {
                File.Delete(savePath);
            }
            File.Copy(tempPath, text);

            List<ExcelData> sumList = MergerService.SumData(list, dataStartRowIndex, dataStartColumnIndex, dataEndRowIndex, dataEndColumnIndex);
            using (new FileStream(tempPath, FileMode.Open, FileAccess.ReadWrite))
            {
                IWorkbook workbook = NPOIUtil.GetWorkbook(text);
                ISheet sheet = NPOIUtil.GetSheet(workbook);
                MergerService.WriteData(sumList, sheet, dataStartRowIndex, dataStartColumnIndex, dataEndRowIndex, dataEndColumnIndex);

                //激活公式
                sheet.ForceFormulaRecalculation = true;

                NPOIUtil.WriteFile(workbook, savePath);
                File.Delete(text);
            }
        }

        private static void WriteData(List<ExcelData> list, ISheet sheet, int dataStartRowIndex, int dataStartColumnIndex,
            int dataEndRowIndex, int dataEndColumnIndex)
        {
        
            //遍历行
            for (int rowIndex = dataStartRowIndex; rowIndex <= dataEndRowIndex; rowIndex++)
            {
                IRow row = NPOIUtil.GetRow(sheet, rowIndex);

                //遍历列
                for(int columnIndex = dataStartColumnIndex; columnIndex <= dataEndColumnIndex; columnIndex++)
                {
                    var cell = NPOIUtil.GetCell(row, columnIndex);
                    if(cell.CellType != CellType.Formula)
                    {
                        cell.SetCellType(CellType.Blank);
                    }
                    var data = list.FirstOrDefault(f => f.RowIndex == rowIndex && f.ColumnIndex == columnIndex);
                    if(data != null)
                    {
                        if (data.ValueInt32 > 0)
                        {
                            NPOIUtil.SetCellValue(cell, data.ValueInt32, null);
                        }
                    }
                  
                }
               
            }
        }

        private static List<ExcelData> SumData(List<ExcelData> list, int dataStartRowIndex, int dataStartColumnIndex,
            int dataEndRowIndex, int dataEndColumnIndex)
        {
            var sumList = new List<ExcelData>();
            if (list == null || list.Count == 0)
            {
                return sumList;
            }
            //遍历行
            for (int rowIndex = dataStartRowIndex; rowIndex <= dataEndRowIndex; rowIndex++)
            {
                //遍历列
                for (int columnIndex = dataStartColumnIndex; columnIndex <= dataEndColumnIndex; columnIndex++)
                {
                    var sumVaule = list.Where(f => f.RowIndex == rowIndex && f.ColumnIndex == columnIndex).Sum(s=>s.ValueInt32);

                    var data = GetData(sumVaule.ToString(), rowIndex, columnIndex);

                    sumList.Add(data);
                }

            }

            return sumList;
        }

        private static ExcelData GetData(string value, int rowIndex, int columnIndex)
        {

            return new ExcelData()
            {
                Value = value,
                ColumnIndex = columnIndex,
                RowIndex = rowIndex,
            };
        }
        
        #endregion

    }
}
